# -*- coding: utf-8 -*-

'''
Created on 22 de Out de 2013

@author: Adbrum
'''
import sqlite3
import xlrd
import codecs, sys
import operator
#import matplotlib.pyplot as plt
import pylab as plt
import numpy as np 
from pylab import *
from xlrd import open_workbook, cellname
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, Float, MetaData, ForeignKey
from sqlalchemy.sql import select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func

sys.stdout = codecs.getwriter('utf8')(sys.stdout) 


db = create_engine('sqlite:///cna.db', encoding='utf-8')
conexao = sqlite3.connect("cna.db")
cursor = conexao.cursor()

db.echo = False  # Try changing this to True and see what happens  
metadata = MetaData(bind=db)
  
cursor.execute('DROP TABLE IF EXISTS resultados')
dados1 = Table('resultados', metadata,
               Column('id', Integer, primary_key=True),
               Column('cod_inst', Integer),
               Column('cod_curso', Integer),
               Column('nome_inst', String(60)),
               Column('nome_curso', String(60)),
               Column('grau', String(2)),
               Column('vagas_init', Integer),
               Column('colocados', Integer),
               Column('media_ult', Float),
               Column('vagas_fim', Integer),
               sqlite_autocomplete=True,
                )
metadata.create_all(db)

# criar a tabela
# Documentação SQLalchemy
Base = declarative_base()
class DadosCandidatura(Base):
    __tablename__ = "resultados"
    id = Column(Integer, primary_key=True)
    cod_inst = Column(Integer)
    cod_curso = Column(Integer)
    nome_inst = Column(String(60))
    nome_curso = Column(String(60))
    grau = Column(String(2))
    vagas_init = Column(Integer)
    colocados = Column(Integer)
    media_ult = Column(Float)
    vagas_fim = Column(Integer)
    
    def __init__(self, tuplo):
        self.cod_inst = tuplo[0]
        self.cod_curso = tuplo[1]
        self.nome_inst = tuplo[2] 
        self.nome_curso = tuplo[3] 
        self.grau = tuplo[4]
        self.vagas_init = tuplo[5]
        self.colocados = tuplo[6] 
        self.media_ult = tuplo[7] 
        self.vagas_fim = tuplo[8] 
        
Session = sessionmaker(bind=db)
session = Session()
# dados = Table('dados_candidatura', metadata, autoload=True)

book = open_workbook('cna131fresultados.xls')
sh = book.sheet_by_index(0)

lista_dados = []

for row in range(4, sh.nrows):
    if (sh.cell_value(row, 0)) == '':
        break 
    else:
        cod_inst = sh.cell_value(row, 0)
        cod_curso = sh.cell_value(row, 1)
        nome_inst = sh.cell_value(row, 2) 
        nome_curso = sh.cell_value(row, 3)
        grau = sh.cell_value(row, 4)
        vagas_init = sh.cell_value(row, 5)
        colocados = sh.cell_value(row, 6)
        if (sh.cell_value(row, 7)) == '':
             media_ult = 0.0
        else:
            media_ult = sh.cell_value(row, 7)
            vagas_fim = sh.cell_value(row, 8)

        tuplo = (cod_inst, cod_curso, nome_inst,
                 nome_curso, grau, vagas_init,
                 colocados, media_ult, vagas_fim)
        # Cria uma lista de listas.
        lista_dados.append(tuplo)

for tuplo in lista_dados:
    dados = DadosCandidatura(tuplo)
    session.add(dados)
    
session.commit()

geral_cod = []
instituto = []

cursor.execute('SELECT DISTINCT(cod_inst), (nome_inst) FROM resultados')
geral_cod = cursor.fetchall()

cursor.execute('SELECT SUM(colocados) FROM resultados')
total = cursor.fetchall()


# Consulta e elimina as repetições dos nomes das instituições.
cursor.execute('SELECT DISTINCT nome_inst FROM resultados')
data = cursor.fetchall()
list, = zip(*data)

# Consulta e elimina as repetições dos codigos das instituições.
cursor.execute('SELECT DISTINCT cod_inst FROM resultados')
cod = cursor.fetchall()

# funçao percentagem para calculo auxiliar
def porcent(a, b):
    d = ((1.0 * b / a) * 100.0)
    return "%.2f" % d

# FUNÇAO PERMILAGEM
def permil(a, b):
    c = ((1.0 * b / a) * 1000.0)
    return '%.2f' % c

# faz a consulta trazendo o resultado onde o valor do nome da instituição tem que ser igual ao indice na lista instituto.
for int, h in enumerate(data):
    cursor.execute('SELECT SUM(colocados) FROM resultados WHERE nome_inst = ?', data[int])
    data1 = cursor.fetchall()
    list1, = zip(*data1)
    
    #print list1 # tuple com soma dos colocados por instituiçao
    
    
    # faz a consulta somando o valor total de alunos colocados
    cursor.execute('SELECT SUM(colocados) FROM resultados')
    data4 = cursor.fetchall()
    
    for numbers in data4[0]:
        total = numbers
         
    for i in (data1):
        
        
        # Chama a função porcente que calcula a porcentagem dos valores enviados.
        percent = porcent(total, i[0])
        
        # número de alunos colocados por instituição;
        #print (instituto[int], i) 

        # percentagem de alunos colocados por instituição em relação a todos os alunos colocados;
        #print (instituto[int], percent)
        

# número de vagas por colocar por instituição;
for int2, h in enumerate(instituto):
    # print int
    cursor.execute('SELECT SUM(vagas_fim) FROM resultados WHERE nome_inst = ?', instituto[int2])
    data2 = cursor.fetchall()
    for i in (data2):
        #print (instituto[int], i)
        pass
 

cursor.execute('ALTER TABLE resultados ADD distrito STRING')  
    
# verifica os distritos
aveiro = zip([300 , 3014 , 3013 , 3012 , 3011 , 4089, 4157 , 4277 , 4283 , 4303 , 4380])
beja = zip([3021, 3022, 7005, 3023])
braga = zip([1000, 3031, 3032, 7010, 2221, 2230, 2241, 2402, 4108, 4011, 4076, 4115, 4460])
braganca = zip([3041, 3045, 3042, 7015, 3043, 4307, 4079, 4099])
cst_brnco = zip([400, 3051, 3055, 3052, 3054, 7020, 3053])
coimbra = zip([508, 501, 502, 503, 504, 505, 506, 507, 7001, 3061, 3062, 3065, 7210, 3063, 3064, 4120, 4126, 4255, 4500])
evora = zip([603, 604, 602, 7030])
faro = zip([200, 201, 203, 204, 3081, 3082, 3087, 7035, 3083, 4290, 4275, 4102])
guarda = zip([3091, 7040, 3092, 3095])
leiria = zip([3103, 3101, 7045, 3102, 3105, 4354, 4292])
lisboa = zip([6800, 700, 5302, 701, 702, 703, 704, 705, 6600, 710, 709, 708, 901, 902, 911, 904, 906, 802, 801, 806, 803, 804, 805, 807, 808, 7002, 7110, 7105, 3110, 3113, 3111, 3112, 3114, 3116, 7220, 3117, 3118, 7500, 7510, 7530, 7540, 4220, 4560, 4295, 4300, 4450, 2700, 2100, 2270, 2220, 2280, 2240, 2295, 4350, 2400, 2800, 4002, 4068, 4074, 4080, 4065, 4096, 4105, 4091, 4104, 4125, 4156, 4160, 4275, 4270, 4298, 4305, 4150, 4385, 4530, 2701, 2215])
portalegre = zip([3123, 3121, 7055, 3122])
porto = zip([1102, 5402, 1103, 1101, 1111, 1114, 1104, 1105, 1106, 1107, 1108, 1113, 1109, 1110, 7003, 3131, 3133, 3132, 3138, 7230, 3134, 3135, 4010, 4061, 4299, 4353, 4358, 4520, 2265, 2210, 2281, 2271, 2289, 2242, 2750, 2401, 4032, 2500, 4109, 4005, 4069, 4078, 4085, 4097, 4101, 4155, 4200, 4271, 4280, 4442, 4441, 4443, 4531, 2216, 2752])
santarem = zip([3141, 3145, 3142, 3143, 7065, 3241, 3243, 3242, 4352, 4095])
setubal = zip([903, 3153, 3151, 3155, 3154, 3152, 7520, 7525, 4260, 4306, 4309, 4077, 4106])
v_castelo = zip([3161, 3164, 3165, 3162, 7075, 3163, 4025, 2751, 2753])
vila_real = zip([1201, 1202, 1205, 1203, 1204, 7080, 4093])
viseu = zip([3185, 3181, 7085, 3186, 3182, 4308, 2225, 4103])
acores = zip([110, 130, 7090, 7091])
madeira = zip([1300, 4098, 4220])



# COMPLETA A TABELA COM OS DADOS INERENTES AO DISTRITO
for ci, g in enumerate(geral_cod):
    cursor.execute('SELECT sum(vagas_fim) FROM resultados WHERE cod_inst = ? ', cod[ci])
    data3 = cursor.fetchall()
    for i in (data3):
        pass
        #print (geral_cod[ci], i)
        if cod[ci] in aveiro:
             for av in aveiro:
                cursor.execute("UPDATE resultados SET distrito = 'AVEIRO' WHERE cod_inst = ?", av)
        elif cod[ci] in beja:
             for av in beja:
                cursor.execute("UPDATE resultados SET distrito = 'BEJA' WHERE cod_inst = ?", av)
        elif cod[ci] in braga:
             for av in braga:
                cursor.execute("UPDATE resultados SET distrito = 'BRAGA' WHERE cod_inst = ?", av)
        elif cod[ci] in braganca:
             for av in braganca:
                cursor.execute("UPDATE resultados SET distrito = 'BRAGANÇA' WHERE cod_inst = ?", av)
        elif cod[ci] in cst_brnco:
             for av in cst_brnco:
                cursor.execute("UPDATE resultados SET distrito = 'CASTELO BRANCO' WHERE cod_inst = ?", av)
        elif cod[ci] in coimbra:
             for av in coimbra:
                cursor.execute("UPDATE resultados SET distrito = 'COIMBRA' WHERE cod_inst = ?", av)
        elif cod[ci] in evora:                  
             for av in evora:
                cursor.execute("UPDATE resultados SET distrito = 'EVORA' WHERE cod_inst = ?", av)
        elif cod[ci] in faro:
             for av in faro:
                cursor.execute("UPDATE resultados SET distrito = 'FARO' WHERE cod_inst = ?", av)
        elif cod[ci] in guarda:
             for av in guarda:
                cursor.execute("UPDATE resultados SET distrito = 'GUARDA' WHERE cod_inst = ?", av)
        elif cod[ci] in leiria:
             for av in leiria:
                cursor.execute("UPDATE resultados SET distrito = 'LEIRIA' WHERE cod_inst = ?", av)
        elif cod[ci] in lisboa:
             for av in lisboa:
                cursor.execute("UPDATE resultados SET distrito = 'LISBOA' WHERE cod_inst = ?", av)
        elif cod[ci] in portalegre:
             for av in portalegre:
                cursor.execute("UPDATE resultados SET distrito = 'PORTALEGRE' WHERE cod_inst = ?", av)
        elif cod[ci] in porto:
             for av in porto:
                cursor.execute("UPDATE resultados SET distrito = 'PORTO' WHERE cod_inst = ?", av)
        elif cod[ci] in santarem:
             for av in santarem:
                cursor.execute("UPDATE resultados SET distrito = 'SANTARÉM' WHERE cod_inst = ?", av)
        elif cod[ci] in setubal:
             for av in setubal:
                cursor.execute("UPDATE resultados SET distrito = 'SETÚBAL' WHERE cod_inst = ?", av)
        elif cod[ci] in v_castelo:
             for av in v_castelo:
                cursor.execute("UPDATE resultados SET distrito = 'VIANA DO CASTELO' WHERE cod_inst = ?", av)
        elif cod[ci] in vila_real:
             for av in vila_real:
                cursor.execute("UPDATE resultados SET distrito = 'VILA REAL' WHERE cod_inst = ?", av)
        elif cod[ci] in viseu:
             for av in viseu:
                cursor.execute("UPDATE resultados SET distrito = 'VISEU' WHERE cod_inst = ?", av)
        elif cod[ci] in acores:
             for av in acores:
                cursor.execute("UPDATE resultados SET distrito = 'AÇORES' WHERE cod_inst = ?", av)
        elif cod[ci] in madeira:
             for av in madeira:
                cursor.execute("UPDATE resultados SET distrito = 'MADEIRA' WHERE cod_inst = ?", av)
        else:
            print 'ERRO'
 
conexao.commit()

# Consulta e elimina as repetições dos nomes distritos.
cursor.execute('SELECT DISTINCT (distrito) FROM resultados')
district = cursor.fetchall()
distrito, = zip(*district)

# número de vagas por colocar por distrito;
vagas_p_dist = []
for int5, t in enumerate(district):
    cursor.execute('SELECT SUM(vagas_fim) FROM resultados WHERE distrito = ?', district[int5])
    data5 = cursor.fetchall()
    for i in (data5):
        vagas_p_dist.append(i[0])
           
# número de colocados por distrito;
colocados_p_dist = []
permilagem = []
for int6, t in enumerate(district):
    cursor.execute('SELECT SUM(colocados) FROM resultados WHERE distrito = ?', district[int6])
    data6 = cursor.fetchall()
    for i in (data6):
        permilage = permil(total, i[0])
    colocados_p_dist.append(i[0])
    permilagem.append(list(map(int,permilage)))
    print permilagem


################## GRAFICOS ############################################
## Credits to http://scienceoss.com/bar-plot-with-custom-axis-labels/ ##

# Colocados por distrito
fig1 = plt.figure()
ax1 = fig1.add_subplot(1,1,1)

# Vagas
fig2 = plt.figure()
ax2 = fig2.add_subplot(1,1,1)

# Permilagem por distrito
fig3 = plt.figure()
ax3 = fig3.add_subplot(1,1,1)
 
# note the change: I'm only supplying y data.
ax1_y = colocados_p_dist
ax2_y = vagas_p_dist
ax3_y = permilagem
 
# Calculate how many bars there will be
ax1_n = len(ax1_y)
ax2_n = len(ax2_y)
ax3_n = len(ax3_y)
 
# Generate a list of numbers, from 0 to N
# This will serve as the (arbitrary) x-axis, which
# we will then re-label manually.
ax1_ind = range(ax1_n)
ax2_ind = range(ax2_n)
ax3_ind = range(ax3_n)

# See note below on the breakdown of this command
ax1.bar(ax1_ind, ax1_y, facecolor='#777777', align='center', ecolor='black')
ax2.bar(ax2_ind, ax2_y, facecolor='#777777', align='center', ecolor='black')
ax3.bar(ax3_ind, ax3_y, facecolor='#777777', align='center', ecolor='black')
 
#Create a yy label
ax1.set_ylabel('Alunos')
ax2.set_ylabel('Vagas')
ax3.set_ylabel('Permilagem')

#Create a xx label
ax1.set_xlabel('Distrito')
ax2.set_xlabel('Distrito')
ax3.set_xlabel('Distrito')
 
# Create a title, in italics
ax1.set_title('Alunos colocados por Distrito',fontstyle='italic')
ax2.set_title('Vagas por Distrito',fontstyle='italic')
ax3.set_title('Permilagem de Colocaçoes por Distrito',fontstyle='italic')
 
# This sets the ticks on the x axis to be exactly where we put
# the center of the bars.
ax1.set_xticks(ax1_ind)
ax2.set_xticks(ax2_ind)
ax3.set_xticks(ax3_ind)
 
# Labels for the ticks on the x axis.  It needs to be the same length
# as y (one label for each bar)
group_labels = distrito
 
# Set the x tick labels to the group_labels defined above.
ax1.set_xticklabels(group_labels, rotation = 90)
ax2.set_xticklabels(group_labels, rotation = 90)
ax3.set_xticklabels(group_labels, rotation = 90)
 
# Extremely nice function to auto-rotate the x axis labels.
# It was made for dates (hence the name) but it works
# for any long x tick labels
#fig.autofmt_xdate()
 
plt.show()

